
package hk.ces.oxfam.patch.transaction;

import hk.ces.oxfam.patch.TransactionPatch;
import hk.ces.oxfam.patch.db.SQLUtil;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.db.sql.PreparedSQL;
import hk.ces.oxfam.patch.xml.SQLResultXML;
import hk.ces.oxfam.patch.xml.factory.SQLResultXMLFactory;
import java.sql.Connection;
import java.sql.SQLException;

/**
 *
 * @author ReutyCheng
 */
public class CheckWalker0001CPatch implements TransactionPatch
{
  public void run(Connection connection) throws SQLException
  {
    SQLResultXMLFactory factory = new SQLResultXMLFactory("check_walker_info_0001");
    factory.setOverwrite(true);
    SQLResultXML[] resultXMLs = new SQLResultXML[]
    {
      new SQLResultXML(this.getRelatedWalkersInfoOf0001(connection))
    };
    factory.setXMLItems(resultXMLs);
    factory.writeToFile();
  }

  private SQLResult getRelatedWalkersOf0001(Connection connection) throws SQLException
  {
    String sql = "SELECT * FROM OTWTEAMMEMBER WHERE ID IN (SELECT MEMBERS_ID FROM OTWTEAM_OTWTEAMMEMBER WHERE OTWTEAM_ID IN (SELECT ID FROM OTWTEAM WHERE TEAMNO = ? AND DISABLED = ?))";
    return SQLUtil.query(connection, new PreparedSQL(sql, new Object[]{"0001", false}));
  }

  private SQLResult getRelatedPersonInfosOf0001C(Connection connection) throws SQLException
  {
    String sql = "SELECT * FROM PERSONINFO WHERE ID IN (SELECT PERSONINFOLIST_ID FROM PERSON_PERSONINFO WHERE PERSON_ID IN (SELECT PERSON_ID FROM OTWTEAMMEMBER WHERE ID = ?))";
    return SQLUtil.query(connection, new PreparedSQL(sql, new Object[]{14316L}));
  }

  private SQLResult getRelatedTransactionOf0001C(Connection connection) throws SQLException
  {
    String sql = "SELECT * FROM TRANSACTION WHERE DONOR_ID IN (SELECT PERSON_ID FROM OTWTEAMMEMBER WHERE ID = ?)";
    return SQLUtil.query(connection, new PreparedSQL(sql, new Object[]{14316L}));
  }

  private SQLResult getRelatedWalkersInfoOf0001(Connection connection) throws SQLException
  {
    String sql = "SELECT DISTINCT * FROM PERSONINFO WHERE ID IN (SELECT PERSONINFOLIST_ID FROM PERSON_PERSONINFO WHERE PERSON_ID IN (SELECT PERSON_ID FROM OTWTEAMMEMBER WHERE DISABLED = 0 AND ID IN (SELECT MEMBERS_ID FROM OTWTEAM_OTWTEAMMEMBER WHERE OTWTEAM_ID IN (SELECT ID FROM OTWTEAM WHERE TEAMNO = ? AND DISABLED = 0)))) AND ID IN (SELECT MAX(PERSONINFOLIST_ID) FROM PERSON_PERSONINFO WHERE PERSONINFOLIST_ID IN (SELECT ID FROM PERSONINFO WHERE PERSONINFOTYPE = ?) GROUP BY PERSON_ID)";
    return SQLUtil.query(connection, new PreparedSQL(sql, new Object[]{"0001", "WALKER"}));
  }
}
